-- Prefan Access Controller
-- SQL and MySQL data system

-- Variables
PAC.MySQL = false
PAC.MySQL_Username = "prefan"
PAC.MySQL_Password = "rosebud"
PAC.MySQL_IP = "db4free.net"
PAC.MySQL_Database = "pac_test"

local query = sql.Query
local db

-- Functions

if PAC.MySQL then 
	require( "mysql" ) 
	db, error = mysql.connect( PAC.MySQL_IP, PAC.MySQL_Username, PAC.MySQL_Password, PAC.MySQL_Database )
	if db == 0 then PAC.MySQL = false print( tostring( error ) ) return end
	
	print( "Connection opened to " .. db .. "!" )
end

function sql.Query( run )

	local result

	if PAC.MySQL then
	
		result = mysql.query( db, run )
		
	else
	
		result = query( run )
		
	end
	
	if result == false then 
		print( "SQL ERRORED! -------------" )
		print( "     RUNNING -- " .. run )
		print( "     RESULT IS -- " .. tostring( result ) )
		print( "     ERROR IS -- " .. sql.LastError( result ) )
		print( "     END OF ERROR REPORT" )
	end
	
	return result
	
end

concommand.Add( "_DropTables", function( ply, _, args )

	sql.Query( "DROP TABLE pac_data_users" )
	sql.Query( "DROP TABLE pac_data_bans" )
	
	PAC.DataInit()
	
end)

function PAC.DataInit()

		sql.Query( "CREATE TABLE IF NOT EXISTS pac_data_users(player text, steam text, uniqueid int, rank text);" )
		sql.Query( "CREATE TABLE IF NOT EXISTS pac_data_bans(player text, steam text, uniqueid int, length int, reason text, bannedby text, bannedat int);" )

end
PAC.DataInit()

concommand.Add( "_DataSave", function( ply, _, args )

	local rank = args[1]
	PAC.SaveUserData( ply, rank )
	
end)

function PAC.SaveBanData( ply, steam, unique, length, reason, bannedby, bannedat )

	ply = ply:Nick()
	
	if not PAC.DataExists( "pac_data_bans", "player", ply ) then

		sql.Query( "INSERT INTO pac_data_bans VALUES (" .. SQLStr( ply ) .. ", " .. SQLStr( steam ) .. ", " .. SQLStr( unique ) .. ", " .. SQLStr( length ) .. ", " .. SQLStr( reason ) .. ", " .. SQLStr( bannedby ) .. ", " .. SQLStr( bannedat ) .. ");" )
	
	else
	
		sql.Query( "UPDATE pac_data_bans SET player = " .. SQLStr( ply ) .. ", steam = " .. SQLStr( steam ) .. ", uniqueid = " .. SQLStr( unique ) .. ", length = " .. SQLStr( length ) .. ", reason = " .. SQLStr( reason ) .. ", bannedby = " .. SQLStr( bannedby ) .. ", bannedat = " .. SQLStr( bannedat ) .. " WHERE player = " .. SQLStr( ply ) .. ";" )

	end
	
	local result = PAC.VerifyData( "pac_data_bans", "player", ply )
	
	if not result then return false end
	
	return true
	
end

function PAC.LoadBanData( ply )

	local ban = sql.Query( "SELECT length FROM pac_data_bans WHERE player = " .. SQLStr( ply ) .. ";" )
	local bannedat = sql.Query( "SELECT bannedat FROM pac_data_bans WHERE player = " .. SQLStr( ply ) .. ";" )
	
	print( ban, bannedat )
	
	if not ban then return false end
	
	local data = {}
		data.banlen = ban[1].length
		data.banat = bannedat[1].bannedat
	
	return data
	
end

function PAC.SaveUserData( ply, steamid, unique, rank )

	ply = ply:Nick()

	if not PAC.DataExists( "pac_data_users", "uniqueid", unique ) then
	
		print( "ADDING" )
		sql.Query( "INSERT INTO pac_data_users VALUES (" .. SQLStr( ply ) .. ", " .. SQLStr( steamid ) .. ", " .. SQLStr( unique ) .. ", " .. SQLStr( rank ) .. ");" )
		
	else

		print( "UPDATING" )
		sql.Query( "UPDATE pac_data_users SET player = " .. SQLStr( ply ) .. ", steam = " .. SQLStr( steamid ) .. ", uniqueid = " .. SQLStr( unique ) .. ", rank = " .. SQLStr( rank ) .. " WHERE uniqueid = " .. SQLStr( unique ) .. ";" )
		
	end
	
	local result = PAC.VerifyData( "pac_data_users", "uniqueid", unique )
	
	if not result then return false end
	
	return true
	
end

concommand.Add( "_LoadAll", function( ply, _, args )
		local rank = sql.Query( "SELECT * FROM pac_data_users;" )
		--print( rank )
		PrintTable( rank )
		
	end)
	
concommand.Add( "_LoadOne", function( ply, _, args )

	local rank = PAC.LoadUserData( ply:UniqueID() )
	print( rank )
	
	end )
	
concommand.Add( "_AddOne", function( ply, _, args )

	PAC.SaveUserData( ply, ply:SteamID(), ply:UniqueID(), "superadmin" )
	
end )

function PAC.LoadUserData( unique )

	local rank = sql.Query( "SELECT rank FROM pac_data_users WHERE uniqueid = " .. SQLStr( unique ) .. ";" )

	if not rank then return false end

	return rank[1].rank
	
end

function PAC.DataExists( tab, where, value )

	return sql.Query( "SELECT " .. where .. " FROM " .. tab .. " WHERE " .. where .. " = " .. SQLStr( value ) .. ";" )
	
end

function PAC.VerifyData( tab, column, value )

	return sql.Query( "SELECT " .. column .. " FROM " .. tab .. " WHERE " .. column .. " = " .. SQLStr( value ) .. ";" )
	
end

function PAC.RemoveData( tab, column, value )

	return sql.Query( "DELETE FROM " .. tab .. " WHERE " .. column .. " = " .. SQLStr( value ) .. ";" )
	
end

function PAC.PurgeData()

	local ah = sql.Query( "DELETE FROM pac_data_users" )
	print( ah )
	
end
concommand.Add( "_Purge", PAC.PurgeData )
